﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class EmailBoxDao
    {
        public bool Delete(SqlConnection conn, int boxId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Email] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = [DeleteFlag] + 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[BoxId] = @BoxId ");
            builder.Append("\tAND [ToId] = @UserId ");
            builder.Append("\tAND [DeleteFlag] IN (0,2) ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Attachment] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[AttachmentId] IN ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t[dbo].[Email] ");
            builder.Append("\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\t[BoxId] = @BoxId ");
            builder.Append("\t\t\t\t\tAND ([ToId] = @UserId) ");
            builder.Append("\t\t\t\t\tAND [DeleteFlag] > 2 ");
            builder.Append("\t\t\t\t\tAND ISNULL([AttachmentId],'') <> '' ");
            builder.Append("\t\t\t\t\tAND [AttachmentId] NOT IN ( ");
            builder.Append("\t\t\t\t\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\t\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t\t\t\t\t[dbo].[Email] ");
            builder.Append("\t\t\t\t\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\t\t\t\t\t[ToId] <> @UserId ");
            builder.Append("\t\t\t\t\t\t\t\t\tAND ISNULL([AttachmentId],'') <> '' ");
            builder.Append("\t\t\t\t\t\t\t \t ) ");
            builder.Append("\t\t\t) ");
            builder.Append("DELETE FROM ");
            builder.Append("\t[dbo].[Email] ");
            builder.Append("WHERE ");
            builder.Append("\t[BoxId] = @BoxId ");
            builder.Append("\tAND ([ToId] = @UserId)\t ");
            builder.Append("\tAND [DeleteFlag] > 2 ");
            builder.Append("DELETE FROM ");
            builder.Append("\t[dbo].[Email_Box] ");
            builder.Append("WHERE ");
            builder.Append("\t[BoxId] = @BoxId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@BoxId", SqlDbType.Int);
            commandParameters[0].Value = boxId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public long InboxSize(SqlConnection conn, string userId)
        {
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            long num = 0L;
            string commandText = "SELECT SUM([TotalSize]) AS Size FROM [dbo].[Email] WHERE [ToId]=@UserId AND [DeleteFlag] IN (0,2)";
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if ((obj2 != null) && (obj2 != DBNull.Value))
            {
                num = Convert.ToInt64(obj2);
            }
            return num;
        }

        public int Insert(SqlConnection conn, EmailBoxData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Email_Box] ");
            builder.Append("( ");
            builder.Append("\t[BoxNo], ");
            builder.Append("\t[BoxName], ");
            builder.Append("\t[UserId] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@BoxNo, ");
            builder.Append("\t@BoxName, ");
            builder.Append("\t@UserId ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@BoxNo", SqlDbType.Int);
            commandParameters[0].Value = data.BoxNo;
            commandParameters[1] = new SqlParameter("@BoxName", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = data.BoxName;
            commandParameters[2] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.UserId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public long OutboxSize(SqlConnection conn, string userId)
        {
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            long num = 0L;
            string commandText = "SELECT SUM([TotalSize]) AS Size FROM [dbo].[Email] WHERE [FromId]=@UserId AND [DeleteFlag] IN (0,1)";
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if ((obj2 != null) && (obj2 != DBNull.Value))
            {
                num = Convert.ToInt64(obj2);
            }
            return num;
        }

        public DataTable SelectAll(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[BoxId], ");
            builder.Append("\t[BoxNo], ");
            builder.Append("\t[BoxName], ");
            builder.Append("\t[UserId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email_Box] ");
            builder.Append("WHERE ");
            builder.Append("\t[UserId] = @UserId ");
            builder.Append("ORDER BY ");
            builder.Append("\t[BoxNo] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Email_Box";
            return set.Tables[0];
        }

        public DataTable SelectByBoxId(SqlConnection conn, int boxId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[BoxId], ");
            builder.Append("\t[BoxNo], ");
            builder.Append("\t[BoxName], ");
            builder.Append("\t[UserId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email_Box] ");
            builder.Append("WHERE ");
            builder.Append("\t[BoxId] = @BoxId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@BoxId", SqlDbType.Int);
            commandParameters[0].Value = boxId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Email_Box";
            return set.Tables[0];
        }

        public long SpecifyBoxSize(SqlConnection conn, string userId, int boxId)
        {
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@BoxId", SqlDbType.Int);
            commandParameters[1].Value = boxId;
            long num = 0L;
            string commandText = "SELECT SUM([TotalSize]) AS Size FROM [dbo].[Email] WHERE [ToId]=@UserId AND [BoxId] = @BoxId AND [DeleteFlag] IN (0,2)";
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if ((obj2 != null) && (obj2 != DBNull.Value))
            {
                num = Convert.ToInt64(obj2);
            }
            return num;
        }

        public DataTable Statistic(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DECLARE @BoxTable TABLE(BoxId int, BoxNo int, BoxName nvarchar(50), Nums int, NewNums int) ");
            builder.Append("DECLARE @BoxId\t\tint ");
            builder.Append("DECLARE @Nums\t\tint ");
            builder.Append("DECLARE @NewNums\t\tint ");
            builder.Append("INSERT INTO @BoxTable ");
            builder.Append("SELECT ");
            builder.Append("\t[BoxId], ");
            builder.Append("\t[BoxNo], ");
            builder.Append("\t[BoxName], ");
            builder.Append("\t0, ");
            builder.Append("\t0 ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email_Box] ");
            builder.Append("WHERE ");
            builder.Append("\t[UserId] = @UserId ");
            builder.Append("ORDER BY ");
            builder.Append("\t[BoxNo] ASC ");
            builder.Append("DECLARE Box_Cursor CURSOR FOR  ");
            builder.Append("\tSELECT [BoxId] FROM @BoxTable ORDER BY [BoxNo] ASC ");
            builder.Append("\tOPEN Box_Cursor ");
            builder.Append("\t ");
            builder.Append("\tFETCH NEXT FROM Box_Cursor INTO @BoxId ");
            builder.Append("\t ");
            builder.Append("\tWHILE @@FETCH_STATUS = 0 ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t@Nums=COUNT([EmailId]) ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Email] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[BoxId] = @BoxId ");
            builder.Append("\t\t\tAND [ToId] = @UserId ");
            builder.Append("\t\t\tAND [DeleteFlag] IN (0,2) ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t@NewNums=COUNT([EmailId]) ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Email] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[BoxId] = @BoxId ");
            builder.Append("\t\t\tAND [ToId] = @UserId ");
            builder.Append("\t\t\tAND [DeleteFlag] IN (0,2) ");
            builder.Append("\t\t\tAND [ReadFlag] = 0 ");
            builder.Append("\t\tUPDATE ");
            builder.Append("\t\t\t@BoxTable ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\tNums = @Nums, ");
            builder.Append("\t\t\tNewNums = @NewNums ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\tBoxId = @BoxId ");
            builder.Append("\t   ");
            builder.Append("\t\tFETCH NEXT FROM Box_Cursor INTO @BoxId ");
            builder.Append("\tEND ");
            builder.Append("\t ");
            builder.Append("\tCLOSE Box_Cursor ");
            builder.Append("\tDEALLOCATE Box_Cursor ");
            builder.Append("SELECT ");
            builder.Append("\tBoxId,  ");
            builder.Append("\tBoxNo, ");
            builder.Append("\tBoxName, ");
            builder.Append("\tNums,  ");
            builder.Append("\tNewNums ");
            builder.Append("FROM  ");
            builder.Append("\t@BoxTable ");
            builder.Append("ORDER BY ");
            builder.Append("\t[BoxNo] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Email_Box";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, EmailBoxData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Email_Box] ");
            builder.Append("SET ");
            builder.Append("\t[BoxNo] = @BoxNo, ");
            builder.Append("\t[BoxName] = @BoxName ");
            builder.Append("WHERE ");
            builder.Append("\t[BoxId] = @BoxId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@BoxId", SqlDbType.Int);
            commandParameters[0].Value = data.BoxId;
            commandParameters[1] = new SqlParameter("@BoxNo", SqlDbType.Int);
            commandParameters[1].Value = data.BoxNo;
            commandParameters[2] = new SqlParameter("@BoxName", SqlDbType.NVarChar, 50);
            commandParameters[2].Value = data.BoxName;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = data.UserId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

